--
-- Definition of function `fn_blog_count`
--

CREATE  FUNCTION `fn_blog_count`(v_month int, v_year int) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT count(blogid) FROM blog WHERE MONTH(createdate) = v_month and YEAR(createdate) = v_year);

	return v_data;

END;

--GO

--
-- Definition of function `fn_combine_tags`
--

CREATE  FUNCTION `fn_combine_tags`(v_itemid VARCHAR(64)) RETURNS text CHARSET latin1
BEGIN

  DECLARE v_data TEXT;
  SET v_data = null;

	SET v_data:= (select CONCAT(tagname, ', ') from tags where itemid = v_itemid);

	return v_data;

END;

--GO

--
-- Definition of function `fn_combine_templatefieldnames`
--

CREATE  FUNCTION `fn_combine_templatefieldnames`(v_templateid VARCHAR(64)) RETURNS text CHARSET latin1
BEGIN

  DECLARE v_data TEXT;
  SET v_data = null;

	SET v_data:= (select v_data+fieldname+',' from templates where templateid = v_templateid order by placeholdernumber);

	return v_data;

END;

--GO

--
-- Definition of function `fn_comment_child_count`
--

CREATE  FUNCTION `fn_comment_child_count`(v_parentid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(id) FROM comments WHERE parentid = v_parentid);

	return v_data;

END;

--GO

--
-- Definition of function `fn_comment_count`
--

CREATE  FUNCTION `fn_comment_count`(v_itemid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(id) FROM comments WHERE itemid = v_itemid);

	return v_data;

END;

--GO

--
-- Definition of function `fn_newmessage_count`
--

CREATE  FUNCTION `fn_newmessage_count`(v_userid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(userprivatemessageid) FROM user_private_message WHERE isviewed = 0 and userid = v_userid);


	return v_data;

END;

--GO

--
-- Definition of function `fn_post_count_bycategory`
--

CREATE  FUNCTION `fn_post_count_bycategory`(v_categoryid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(p.postid) from forum_post as p join forum_topic as t on t.topicid = p.topicid join forum_forum as f on f.forumid = t.forumid	join forum_category as c on c.categoryid = f.categoryid WHERE c.categoryid = v_categoryid and p.isapproved = 1);

	return v_data;

END;

--GO

--
-- Definition of function `fn_post_count_byforum`
--

CREATE  FUNCTION `fn_post_count_byforum`(v_forumid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(p.postid) from forum_post as p join forum_topic as t on t.topicid = p.topicid join forum_forum as f on f.forumid = t.forumid WHERE f.forumid = v_forumid and p.isapproved = 1);

	return v_data;

END;

--GO

--
-- Definition of function `fn_post_count_bytopic`
--

CREATE  FUNCTION `fn_post_count_bytopic`(v_topicid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(p.postid) from forum_post as p join forum_topic as t on t.topicid = p.topicid WHERE t.topicid = v_topicid and p.isapproved = 1);

	return v_data;

END;

--GO

--
-- Definition of function `fn_post_count_byuser`
--

CREATE  FUNCTION `fn_post_count_byuser`(v_userid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(postid) from forum_post WHERE userid = v_userid and isapproved = 1);

	return v_data;

END;

--GO

--
-- Definition of function `fn_post_get_categoryid_byboard`
--

CREATE  FUNCTION `fn_post_get_categoryid_byboard`(v_boardid VARCHAR(64)) RETURNS varchar(64) CHARSET latin1
BEGIN

  DECLARE v_data VARCHAR(64);
  SET v_data = null;

	SET v_data:= (SELECT categoryid FROM forum_category WHERE boardid = v_data and categoryid Is Not Null);

	return v_data;

END;

--GO

--
-- Definition of function `fn_rank_count`
--

CREATE  FUNCTION `fn_rank_count`(v_itemid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT AVG(rank) FROM ranks WHERE itemid = v_itemid);

	return v_data;

END;

--GO

--
-- Definition of function `fn_tagname_count`
--

CREATE  FUNCTION `fn_tagname_count`(v_tagname VARCHAR(8000)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(tagname) FROM tags WHERE tagname = v_tagname and archived <> 1);

	return v_data;

END;

--GO

--
-- Definition of function `fn_tagname_count_by_item`
--

CREATE  FUNCTION `fn_tagname_count_by_item`(v_tagname VARCHAR(8000), v_itemid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(tagname) FROM tags WHERE tagname = v_tagname and itemid = v_itemid and archived <> 1	);

	return v_data;

END;

--GO

--
-- Definition of function `fn_tagname_count_by_user`
--

CREATE  FUNCTION `fn_tagname_count_by_user`(v_tagname VARCHAR(8000), v_userid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(tagname) FROM tags WHERE tagname = v_tagname and userid = v_userid and archived <> 1	);

	return v_data;

END;

--GO

--
-- Definition of function `fn_tagname_count_by_user_item`
--

CREATE  FUNCTION `fn_tagname_count_by_user_item`(v_tagname VARCHAR(8000), v_userid VARCHAR(64), v_itemid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(tagname) FROM tags WHERE tagname = v_tagname and userid = v_userid and itemid = v_itemid and archived <> 1	);

	return v_data;

END;

--GO

--
-- Definition of function `fn_template_usage_count`
--

CREATE  FUNCTION `fn_template_usage_count`(v_templateid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(*) FROM page WHERE templateid = v_templateid);

	return v_data;

END;

--GO

--
-- Definition of function `fn_topic_count_bycategory`
--

CREATE  FUNCTION `fn_topic_count_bycategory`(v_categoryid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(t.topicid) FROM forum_topic as t join forum_forum as f on f.forumid = t.forumid join forum_category as c on c.categoryid = f.categoryid WHERE c.categoryid = v_categoryid and t.isapproved = 1);

	return v_data;

END;

--GO

--
-- Definition of function `fn_topic_count_byforum`
--

CREATE  FUNCTION `fn_topic_count_byforum`(v_forumid VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT COUNT(t.topicid) from forum_topic as t join forum_forum as f on f.forumid = t.forumid WHERE f.forumid = v_forumid and t.isapproved = 1);

	return v_data;

END;

--GO

--
-- Definition of function `fn_topic_lastpostdate`
--

CREATE  FUNCTION `fn_topic_lastpostdate`(v_topicid VARCHAR(64)) RETURNS datetime
BEGIN

  DECLARE v_data DATETIME;
  SET v_data = null;

	SET v_data:= (SELECT p.created FROM forum_post as p WHERE topicid = v_topicid ORDER BY p.created DESC LIMIT 1);

	return v_data;

END;

--GO

--
-- Definition of function `fn_topic_lastpostid`
--

CREATE  FUNCTION `fn_topic_lastpostid`(v_topicid VARCHAR(64)) RETURNS varchar(64) CHARSET latin1
BEGIN

  DECLARE v_data VARCHAR(64);
  SET v_data = null;

	SET v_data:= (SELECT p.userid FROM forum_post as p WHERE topicid = v_topicid ORDER BY p.created DESC LIMIT 1);

	return v_data;

END;

--GO

--
-- Definition of function `fn_view_count`
--

CREATE  FUNCTION `fn_view_count`(v_itemid  VARCHAR(64)) RETURNS int(11)
BEGIN

  DECLARE v_data INT;
  SET v_data = 0;

	SET v_data:= (SELECT viewcount FROM views WHERE itemid = v_itemid);

	return v_data;

END;
